import json
import hashlib
import time
import requests
import pymysql
from requests.adapters import HTTPAdapter
from urllib3.util.retry import Retry

def load_config_file(json_file='../configs/test.json'):
    try:
        with open(json_file, 'r', encoding='utf-8') as file:
            data = json.load(file)
    except FileNotFoundError as e:
        print("配置文件 %s 未找到: %s", json_file, e)
        raise  # 抛出异常，停止后续执行
    except json.JSONDecodeError as e:
        print("解析配置文件 %s 时出错: %s", json_file, e)
        raise  # 抛出异常，停止后续执行
    except Exception as e:
        print("读取配置文件时发生未知错误: %s", e)
        raise  # 抛出异常，停止后续执行
    return data

def initial_load(config_path='../configs/test.json'):
    data = load_config_file(json_file=config_path)
    current_date = time.strftime('%Y-%m-%d')
    base_url = data['base_url']
    group_code = data['group_code']
    mapping_path = data['mapping_path']
    aeskey = data['aeskey']
    headers = {
        'Group-Code': group_code,
        'Mapping-Path': mapping_path,
        'Data-Signature': generate_sign(group_code, mapping_path, aeskey, current_date)
    }

    # 数据库连接配置
    db_config_path = load_config_file(json_file="/opt/wyh/LSP_book_rec/global_configs/mysql_db_config.json")
    print("db", db_config_path['database']['host'])
    conn = pymysql.connect(
        host=db_config_path['database']['host'],
        user=db_config_path['database']['user'],
        passwd=db_config_path['database']['password'],
        port=db_config_path['database']['port'],
        db=db_config_path['database']['db'],  # 连接到数据库
        charset=db_config_path['database']['charset']
    )
    print("suc")
    return base_url,headers,conn

def load_script():
    sql_user = """
        CREATE TABLE `all_user_data`(
            `USER_ID` INT NOT NULL AUTO_INCREMENT,
            `GENDER` VARCHAR(255),
            `COLLEGE_YEAR_ID` INT,
            `COLLEGE_DEPT_ID` INT,
            `MAJOR_ID` INT,
            `TOTAL_LEND` INT,
            `RESEARCHER_FLAG` CHAR(1),
            `EDU_ID` INT,
            `COLLEGE_CLASS_ID` INT,
            `UPDATE_DATE` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新日期',
            `user_hist` TEXT,
            PRIMARY KEY (`USER_ID`),
            KEY `IDX_UPDATE_DATE` (`UPDATE_DATE`)

        )ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户数据表'
        """
    sql_item = """
        CREATE TABLE `all_item_data` (
        `ITEM_ID` INT NOT NULL AUTO_INCREMENT,
        `CUR_LOCATION_ID` INT,
        `TOTAL_CIRC_COUNT` INT,
        `GET_MODE_CODE` INT,
        `CIRC_NUMBER` INT,
        `RECORD_ID` INT,
        `UPDATE_DATE` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新日期',
        `tag` VARCHAR(255),
        PRIMARY KEY (ITEM_ID),
        KEY `IDX_UPDATE_DATE` (`UPDATE_DATE`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='物品数据表'
    
    """
    sql_book = """
        CREATE TABLE `book_attr` (
    `RECORD_ID` bigint(20) NOT NULL,
    `CALL_NO` varchar(255) DEFAULT NULL,
    `TITLE` varchar(1000) DEFAULT '',
    `TITLE_S` varchar(2000) DEFAULT '' COMMENT '题名(格式化)',
    `ISBN13` varchar(255) DEFAULT '',
    `ISBN10` varchar(255) DEFAULT '',
    `ISBN` varchar(255) DEFAULT '',
    `AUTHOR` varchar(500) DEFAULT '' COMMENT '责任者',
    `PUBLISHER` varchar(1000) DEFAULT '',
    `PUB_DATE` varchar(50) DEFAULT '',
    `PUB_YEAR` varchar(4) DEFAULT NULL COMMENT '出版年',
    `SERIRL_FLAG` char(1) DEFAULT NULL,
    `PARTITIONER` varchar(50) DEFAULT NULL COMMENT '分辑',
    `VERSION` varchar(50) DEFAULT NULL COMMENT '版本',
    `SERIES` varchar(50) DEFAULT NULL COMMENT '丛书名',
    `POST_ISSUE_NO` varchar(100) DEFAULT NULL COMMENT '邮发代号(多值情况下以“u001F”分隔)',
    `CN_NO` varchar(50) DEFAULT NULL COMMENT '国内统一书刊号',
    `E_ISBN` varchar(255) DEFAULT '' COMMENT ' 电子标准号',
    `VERSION_EXP` varchar(50) DEFAULT NULL COMMENT '版次说明',
    PRIMARY KEY (`RECORD_ID`),
    KEY `IDX_ISBN10` (`ISBN10`),
    KEY `IDX_ISBN` (`ISBN`) USING BTREE,
    KEY `IDX_ISBN13` (`ISBN13`),
    KEY `IDX_E_ISBN` (`E_ISBN`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='书的属性信息'
    
    """
    sql_meta = """
        CREATE TABLE `metadata` (
    `MD_ID` int(11) NOT NULL DEFAULT '-1',
    `FIELD` varchar(100) NOT NULL DEFAULT '',
    `FIELD_DATA` text NOT NULL,
    `RECORD_ID` bigint(20) NOT NULL DEFAULT '-1',
    `LIB_CODE` varchar(20) NOT NULL DEFAULT '',
    `GROUP_CODE` varchar(20) NOT NULL DEFAULT '',
    `CREATE_BY` int(11) NOT NULL DEFAULT '-1',
    `CREATE_DATE` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建日期',
    `UPDATE_BY` int(11) NOT NULL DEFAULT '-1',
    `UPDATE_DATE` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新日期',
    PRIMARY KEY (`MD_ID`,`RECORD_ID`),
    KEY `FK_METADATA_BIB` (`RECORD_ID`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='书的详细信息'
    
    """
    return sql_user, sql_item, sql_book, sql_meta

def generate_sign(group_code, mapping_path, aeskey, date):
    sign_str = f'{mapping_path}{group_code}{aeskey}{date}'
    return hashlib.md5(sign_str.encode('utf-8')).hexdigest().upper()


# 创建带重试的 HTTP 请求会话
def create_retry_session(retries=5, backoff_factor=0.3, status_forcelist=(500, 502, 504)):
    session = requests.Session()
    retry = Retry(
        total=retries,
        read=retries,
        connect=retries,
        backoff_factor=backoff_factor,
        status_forcelist=status_forcelist,
    )
    adapter = HTTPAdapter(max_retries=retry)
    session.mount('http://', adapter)
    session.mount('https://', adapter)
    return session

session = create_retry_session(retries=5, backoff_factor=0.5)

# 这里初始值的设定是因为cupl从21.9.30号才开始接入系统的，前面的数据比较混乱，舍弃了
def fetch_loan_history(base_url, headers, loan_hist_id=12304098, rows=2000):
    api_uri = "/dataopen/sync/syncLoanHistory"
    params = { 
        "loanHistId": loan_hist_id, 
        "rows": rows
    }

    response = requests.post(base_url + api_uri, headers=headers, json=params)
    if response.status_code == 200:
        return response.json()
    else:
        print(f"API请求失败，状态码：{response.status_code}")
        return None

def fetch_user(base_url,headers,user_id=-1000,rows=2000):
    api_uri = "/dataopen/sync/syncUser"
    params = {
        "userId": user_id,
        "rows": rows
    }
    response = requests.post(base_url + api_uri, headers=headers, json=params)
    if response.status_code == 200:
        return response.json()
    else:
        print(f"API请求失败，状态码：{response.status_code}")
        return None

def fetch_item(base_url,headers,item_id=-1,rows=2000):
    api_uri = "/dataopen/sync/syncPhysicalItem"
    params = {
        "itemId": item_id,
        "rows": rows
    }

    response = requests.post(base_url + api_uri, headers=headers, json=params)
    if response.status_code == 200:
        return response.json()
    else:
        print(f"API请求失败，状态码：{response.status_code}")
        return None

def fetch_book_attr(base_url,headers,record_id=-1,rows=2000):
    api_uri = "/dataopen/sync/syncBookAttr"
    params = {
        "recordId": record_id,
        "rows": rows
    }

    response = requests.post(base_url + api_uri, headers=headers, json=params)
    if response.status_code == 200:
        return response.json()
    else:
        print(f"API请求失败，状态码：{response.status_code}")
        return None




def fetch_metadata(base_url, headers, metadata_id=-1, record_id=-1, rows=2000):
    """
        从 API 拉取元数据
        :param base_url: API 基础 URL
        :param headers: 请求头
        :param metadata_id: 元数据的起始 ID
        :param record_id: 记录的起始 ID
        :param rows: 每次请求的最大数据量
        :return: API 返回的 JSON 数据
    """
    api_uri = "/dataopen/sync/syncMetadata"
    params = {
        "mdId": metadata_id,  # 起始元数据 ID
        "recordId": record_id,  # 起始记录 ID
        "rows": rows  # 拉取数据量
    }
    result = {}
    try:
        # 发起 POST 请求，设置超时时间
        response = session.post(base_url + api_uri, headers=headers, json=params, timeout=10)

        # 如果状态码为 200，处理响应
        if response.status_code == 200:
            try:
                # 尝试解析 JSON 数据
                result = response.json()

                # 校验返回数据格式
                if "success" in result and "data" in result:
                    return result
                else:
                    print(f"API返回格式不符合预期：{result}")
                    return {"success": False, "data": None}
            except ValueError as e:
                # JSON 解析错误
                print(f"解析 JSON 响应时出错: {e}")
                return {"success": False, "data": None}
        else:
            # 非 200 状态码处理
            print(f"API请求失败，状态码：{response.status_code}, 响应：{response.text}")
            return {"success": False, "data": None}

    except requests.exceptions.Timeout:
        # 请求超时
        print("请求超时，请检查网络连接或服务器状态。")
        return {"success": False, "data": None}

    except Exception as e:
        # 捕获其他网络请求异常
        print(f"请求失败：{e}")
        return {"success": False, "data": None}
    
def execute_sql_script(sql_script, db_config):
    connection = pymysql.connect(**db_config)
    try:
        with connection.cursor() as cursor:
            cursor.execute(sql_script)
            connection.commit()
            print("表创建成功。")
    except pymysql.Error as e:
        print(f"执行SQL时发生错误: {e}")
    finally:
        connection.close()

def create_sql_database(conn,groupName):
    """
        创建sql database
    """
    try:
        with conn.cursor() as cursor:
            sql_user,sql_item,sql_book,sql_meta = load_script()
            try:
                sql_script = sql_user.replace('all_user_data', f'{groupName}_all_user_data')
                cursor.execute(sql_script)
            except pymysql.err.OperationalError as e:
                print("error:",e)
            try:
                sql_script = sql_item.replace('all_item_data', f'{groupName}_all_item_data')
                cursor.execute(sql_script)
            except pymysql.err.OperationalError as e:
                print("error:",e)
            try:
                sql_script = sql_book.replace('book_attr', f'{groupName}_book_attr')
                cursor.execute(sql_script)
            except pymysql.err.OperationalError as e:
                print("error:",e)
            try:
                sql_script = sql_meta.replace('metadata', f'{groupName}_metadata')
                cursor.execute(sql_script)
            except pymysql.err.OperationalError as e:
                print("error:",e)
            # 执行SQL脚本创建表
            print(f"表 {groupName}_all_user_data  {groupName}_all_item_data {groupName}_book_attr 和 {groupName}_metadata 已创建成功。")
    except Exception as e:
        print(f"发生错误：{e}")

if __name__ == "__main__":

    base_url,headers,conn = initial_load('../configs/test.json')
    result = fetch_user(base_url,headers,user_id=140873,rows=3)
    print(result)